﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Data.SqlClient;
using OneDrive.Model;
using Maticsoft.DBUtility;

namespace OneDrive.DAO
{
    public partial class HouseInfoDAO
    {
        #region  BasicMethod

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("HouseID", "tbl_HouseInfo");
        }

        public static bool Clear()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbl_HouseInfo ");

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int iHouseID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbl_HouseInfo");
            strSql.Append(" where HouseID=@HouseID");
            SqlParameter[] parameters = {
                    new SqlParameter("@HouseID", SqlDbType.Int)
            };
            parameters[0].Value = iHouseID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(HouseInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tbl_HouseInfo(");
            strSql.Append("HouseID,CommunityID,AgentID,Tittle,ReleaseTime,HouseHold,BuiltupArea,InnerArea,Floor,Orientation,Type,Decorate,Limit,OnlyHouse,TotalPrice,PropertyYears,Platform)");
            strSql.Append(" values (");
            strSql.Append("@HouseID,@CommunityID,@AgentID,@Tittle,@ReleaseTime,@HouseHold,@BuiltupArea,@InnerArea,@Floor,@Orientation,@Type,@Decorate,@Limit,@OnlyHouse,@TotalPrice,@PropertyYears,@Platform)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@HouseID", SqlDbType.Int),
                    new SqlParameter("@CommunityID", SqlDbType.Int),
                    new SqlParameter("@AgentID", SqlDbType.Int),
                    new SqlParameter("@Tittle", SqlDbType.NVarChar,50),
                    new SqlParameter("@ReleaseTime", SqlDbType.Date),
                    new SqlParameter("@HouseHold", SqlDbType.NVarChar,20),
                    new SqlParameter("@BuiltupArea", SqlDbType.Float),
                    new SqlParameter("@InnerArea", SqlDbType.Float),
                    new SqlParameter("@Floor", SqlDbType.NVarChar,20),
                    new SqlParameter("@Orientation", SqlDbType.NVarChar,10),
                    new SqlParameter("@Type", SqlDbType.NVarChar,20),
                    new SqlParameter("@Decorate", SqlDbType.NVarChar,20),
                    new SqlParameter("@Limit", SqlDbType.NVarChar,20),
                    new SqlParameter("@OnlyHouse", SqlDbType.Int),
                    new SqlParameter("@TotalPrice", SqlDbType.Float),
                    new SqlParameter("@PropertyYears", SqlDbType.Int),
                    new SqlParameter("@Platform", SqlDbType.Int)};
            parameters[0].Value = model.HouseID;
            parameters[1].Value = model.CommunityID;
            parameters[2].Value = model.AgentID;
            parameters[3].Value = model.Tittle;
            parameters[4].Value = model.ReleaseTime;
            parameters[5].Value = model.HouseHold;
            parameters[6].Value = model.BuiltupArea;
            parameters[7].Value = model.InnerArea;
            parameters[8].Value = model.Floor;
            parameters[9].Value = model.Orientation;
            parameters[10].Value = model.Type;
            parameters[11].Value = model.Decorate;
            parameters[12].Value = model.Limit;
            parameters[13].Value = model.OnlyHouse;
            parameters[14].Value = model.TotalPrice;
            parameters[15].Value = model.PropertyYears;
            parameters[16].Value = model.Platform;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(HouseInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbl_HouseInfo set ");
            strSql.Append("HouseID=@HouseID,");
            strSql.Append("CommunityID=@CommunityID,");
            strSql.Append("AgentID=@AgentID,");
            strSql.Append("Tittle=@Tittle,");
            strSql.Append("ReleaseTime=@ReleaseTime,");
            strSql.Append("HouseHold=@HouseHold,");
            strSql.Append("BuiltupArea=@BuiltupArea,");
            strSql.Append("InnerArea=@InnerArea,");
            strSql.Append("Floor=@Floor,");
            strSql.Append("Orientation=@Orientation,");
            strSql.Append("Type=@Type,");
            strSql.Append("Decorate=@Decorate,");
            strSql.Append("Limit=@Limit,");
            strSql.Append("OnlyHouse=@OnlyHouse,");
            strSql.Append("TotalPrice=@TotalPrice,");
            strSql.Append("PropertyYears=@PropertyYears,");
            strSql.Append("Platform=@Platform,");

            SqlParameter[] parameters = {
                    new SqlParameter("@HouseID", SqlDbType.Int),
                    new SqlParameter("@CommunityID", SqlDbType.Int),
                    new SqlParameter("@AgentID", SqlDbType.Int),
                    new SqlParameter("@Tittle", SqlDbType.NVarChar,50),
                    new SqlParameter("@ReleaseTime", SqlDbType.Date),
                    new SqlParameter("@HouseHold", SqlDbType.NVarChar,20),
                    new SqlParameter("@BuiltupArea", SqlDbType.Float),
                    new SqlParameter("@InnerArea", SqlDbType.Float),
                    new SqlParameter("@Floor", SqlDbType.NVarChar,20),
                    new SqlParameter("@Orientation", SqlDbType.NVarChar,10),
                    new SqlParameter("@Type", SqlDbType.NVarChar,20),
                    new SqlParameter("@Decorate", SqlDbType.NVarChar,20),
                    new SqlParameter("@Limit", SqlDbType.NVarChar,20),
                    new SqlParameter("@OnlyHouse", SqlDbType.Int),
                    new SqlParameter("@TotalPrice", SqlDbType.Float),
                    new SqlParameter("@PropertyYears", SqlDbType.Int),
                    new SqlParameter("@Platform", SqlDbType.Int)};

            parameters[0].Value = model.HouseID;
            parameters[1].Value = model.CommunityID;
            parameters[2].Value = model.AgentID;
            parameters[3].Value = model.Tittle;
            parameters[4].Value = model.ReleaseTime;
            parameters[5].Value = model.HouseHold;
            parameters[6].Value = model.BuiltupArea;
            parameters[7].Value = model.InnerArea;
            parameters[8].Value = model.Floor;
            parameters[9].Value = model.Orientation;
            parameters[10].Value = model.Type;
            parameters[11].Value = model.Decorate;
            parameters[12].Value = model.Limit;
            parameters[13].Value = model.OnlyHouse;
            parameters[14].Value = model.TotalPrice;
            parameters[15].Value = model.PropertyYears;
            parameters[16].Value = model.Platform;


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int iHouseID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbl_HouseInfo ");
            strSql.Append(" where HouseID=@HouseID");
            SqlParameter[] parameters = {
                    new SqlParameter("@HouseID", SqlDbType.Int)
            };
            parameters[0].Value = iHouseID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string strHouseIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbl_HouseInfo ");
            strSql.Append(" where HouseID in (" + strHouseIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public HouseInfo GetModel(int iHouseID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 HouseID,CommunityID,AgentID,Tittle,ReleaseTime,HouseHold,BuiltupArea,InnerArea,Floor,Orientation,Type,Decorate,Limit,OnlyHouse,TotalPrice,PropertyYears,Platform from tbl_HouseInfo ");
            strSql.Append(" where HouseID=@HouseID");
            SqlParameter[] parameters = {
                    new SqlParameter("@HouseID", SqlDbType.Int)
            };
            parameters[0].Value = iHouseID;

            HouseInfo model = new HouseInfo();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public HouseInfo DataRowToModel(DataRow row)
        {
            HouseInfo model = new HouseInfo();
            if (row != null)
            {
                if (row["HouseID"] != null && row["HouseID"].ToString() != "")
                {
                    model.HouseID = int.Parse(row["HouseID"].ToString());
                }
                if (row["CommunityID"] != null && row["CommunityID"].ToString() != "")
                {
                    model.CommunityID = int.Parse(row["CommunityID"].ToString());
                }
                if (row["AgentID"] != null && row["AgentID"].ToString() != "")
                {
                    model.AgentID = int.Parse(row["AgentID"].ToString());
                }
                if (row["Tittle"] != null)
                {
                    model.Tittle = row["Tittle"].ToString();
                }
                if (row["ReleaseTime"] != null && row["ReleaseTime"].ToString() != "")
                {
                    model.ReleaseTime = DateTime.Parse(row["ReleaseTime"].ToString());
                }
                if (row["HouseHold"] != null)
                {
                    model.HouseHold = row["HouseHold"].ToString();
                }
                if (row["BuiltupArea"] != null && row["BuiltupArea"].ToString() != "")
                {
                    model.BuiltupArea = float.Parse(row["BuiltupArea"].ToString());
                }
                if (row["InnerArea"] != null && row["InnerArea"].ToString() != "")
                {
                    model.InnerArea = float.Parse(row["InnerArea"].ToString());
                }
                if (row["Floor"] != null)
                {
                    model.Floor = row["Floor"].ToString();
                }
                if (row["Orientation"] != null)
                {
                    model.Orientation = row["Orientation"].ToString();
                }
                if (row["Type"] != null)
                {
                    model.Type = row["Type"].ToString();
                }
                if (row["Decorate"] != null)
                {
                    model.Decorate = row["Decorate"].ToString();
                }
                if (row["Limit"] != null)
                {
                    model.Limit = row["Limit"].ToString();
                }
                if (row["OnlyHouse"] != null)
                {
                    model.OnlyHouse = int.Parse(row["OnlyHouse"].ToString());
                }
                if (row["TotalPrice"] != null)
                {
                    model.TotalPrice = float.Parse(row["TotalPrice"].ToString());
                }
                if (row["PropertyYears"] != null)
                {
                    model.PropertyYears = int.Parse(row["PropertyYears"].ToString());
                }
                if (row["Platform"] != null)
                {
                    model.Platform = (Platform)Enum.Parse(typeof(Platform), row["Platform"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select HouseID,CommunityID,AgentID,Tittle,ReleaseTime,HouseHold,BuiltupArea,InnerArea,Floor,Orientation,Type,Decorate,Limit,OnlyHouse,TotalPrice,PropertyYears,Platform ");
            strSql.Append(" FROM tbl_HouseInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" HouseID,CommunityID,AgentID,Tittle,ReleaseTime,HouseHold,BuiltupArea,InnerArea,Floor,Orientation,Type,Decorate,Limit,OnlyHouse,TotalPrice,PropertyYears,Platform ");
            strSql.Append(" FROM tbl_HouseInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM tbl_HouseInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.CID desc");
            }
            strSql.Append(")AS Row, T.*  from Course T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<HouseInfo> DataTableToList(DataTable dt)
        {
            List<HouseInfo> modelList = new List<HouseInfo>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                HouseInfo model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = DataRowToModel(dt.Rows[n]);
                    if (model != null)
                    {
                        modelList.Add(model);
                    }
                }
            }
            return modelList;
        }
        #endregion  BasicMethod

    }
}
